import sys
sys.path.append("../")
import pandas as pd
import numpy as np
import os
import pymysql
from datetime import datetime, date
# from container.wind_id_data import name_and_id
# from mongodb.wind_download_data import download_from_id
from tjdutils.utils import TjdDate
import numpy as np
import time
import multiprocessing as mp

class y_assemble_update():
    def __init__(self):
        self.host = '192.168.1.145'
        self.port = 3306
        self.user = 'root'
        self.password = '280303'
        self.db = 'y_assemble'
        self.charset = "utf8"

    def connect(self):
        self.conn = pymysql.connect(host=self.host, port=self.port, user=self.user, 
                                    password=self.password, db=self.db,
                                    charset=self.charset)

    def execute_and_disconnect(self):
        self.conn.ping(reconnect=True)
        self.conn.commit()
        self.conn.close()

    def exec_sql(self, sql, value=()):
        cur = self.conn.cursor()
        cur.execute(sql, value)
        res = cur.fetchall()
        cur.close()
        return res

    def generator(self, num):
        # 生成num个%s的字符串，用于编写mysql语句
        s_chain = ['%s']
        for i in range(num - 1):
            s_chain.insert(len(s_chain), '%s')
        s_res = ','.join(s_chain)
        s_res = '(' + s_res + ')'
        return s_res

    def basic_update(self, y_type, y_path):
        select_table_sql = "select table_name from information_schema.tables where table_schema='" + self.db + "'"
        res = self.exec_sql(select_table_sql)
        table_names = []
        for i in range(len(res)):
            table_names.append(res[i][0])

        if y_type not in table_names:
            create_table_sql = "create table `" + y_type + "` as select * from `" + table_names[0] + "` where 1=2"
            self.exec_sql(create_table_sql)
            print('新表\"' + y_type + '\"已经新增')

        select_columns_sql = "select count(*) from information_schema.COLUMNS where TABLE_SCHEMA = '"+ self.db + "' and table_name = '" + y_type + "'"
        res = self.exec_sql(select_columns_sql)

        data = pd.read_excel(y_path, index_col=0).T
        if res[0][0] != data.shape[1] + 1:
            table_columns_sql = 'y_name varchar(255) primary key'
            for column in data.columns.values:
                table_columns_sql += ', `' + str(column)[:10] + '` float'
            table_columns_sql = '(' + table_columns_sql + ')'

            drop_table_sql = "drop table `" + y_type + "`"
            self.exec_sql(drop_table_sql)

            create_table_sql = "create table " + y_type + " " + table_columns_sql
            self.exec_sql(create_table_sql)

        else:
            delete_table_sql = "delete from `" + y_type + "`"
            self.exec_sql(delete_table_sql)

        for i in range(data.shape[0]):
            update_table_sql = "insert into `" + y_type + "` values " + self.generator(data.shape[1] + 1)
            update_table_value = [data.index.values[i]] + [float(data.iloc[i][j]) if pd.isnull(data.iloc[i][j]) == False else None for j in range(data.shape[1])]
            update_table_value = tuple(update_table_value)
            self.exec_sql(update_table_sql, update_table_value)


    def update(self, y_name_list, y_path_list):
        self.connect()
        for i in range(len(y_name_list)):
            y_name = y_name_list[i]
            y_path = y_path_list[i]
            self.basic_update(y_name, y_path)
            print('\"' + y_name + '\"更新完毕')
        self.execute_and_disconnect()


    def select_table(self, y_type):
        self.connect()
        select_data_sql = "select * from " + y_type
        data = self.exec_sql(select_data_sql)
        data_dict = {}
        for i in range(len(data)):
            data_dict[data[i][0]] = list(data[i][1:])
        data_df = pd.DataFrame(data_dict, index=pd.date_range(start='2008-01-31', periods=len(data[0])-1, freq='M'))
        return data_df

    def select_all_table(self):
        self.connect()
        select_table_sql = "select table_name from information_schema.tables where table_schema='" + self.db + "'"
        res = self.exec_sql(select_table_sql)
        tables = [table[0] for table in res]
        res_df = []
        for table in tables:
            basic_res = self.select_table(table)
            res_df.append(basic_res)


        res = pd.concat(res_df, axis=1)
        self.execute_and_disconnect()
        return res


def update_wind_key_targets():
    Update = y_assemble_update()
    # Update.update(y_type_list, y_path_list)
    result = Update.select_table('yoy')
    result.index = [str(s)[:10] for s in result.index]
    data = download_from_id(list(name_and_id.keys()))
    # result就是y集合的数据库版
    new_name = [name_and_id[key] for key in data.columns]
    data.columns = new_name
    data.index = [str(s)[:10] for s in data.index]
    log = []
    for y_name in result.columns:
        if y_name in new_name:
            print('正在检查%s是否更新'%y_name)
            t = date.today()
            # 生成最近三个月的月底值（从今天算起往前数）
            t_list = [
                TjdDate(t).dt['lmld'], 
                TjdDate(TjdDate(t).dt['lmld']).dt['lmld'],
                TjdDate(TjdDate(TjdDate(t).dt['lmld']).dt['lmld']).dt['lmld'],
                      ]
            for t_l in t_list:
                t_l = str(t_l)[:10]
                if t_l in result.index:
                    value_in_mysql = result.loc[t_l, y_name]
                    if np.isnan(value_in_mysql):
                        # 判断库中这个日期的数据是不是nan,是nan说明没有数据
                        value_in_wind = data.loc[t_l, y_name]
                        if not np.isnan(value_in_wind):
                            result.loc[t_l, y_name] = float(value_in_wind) / 100
                            s_log = ">>>更新指标为%s,日期:%s,值:%s" % (y_name, t_l, str(float(value_in_wind) / 100))
                            log.append(s_log)
                            print(s_log)
                else:
                    append_result_data = pd.DataFrame(np.nan, index=[t_l], columns=result.columns)
                    result = pd.concat([result, append_result_data], axis=0)
                    print("新增日期", t_l)
    return result, data
    
    


if __name__ == '__main__':
    Update = y_assemble_update()
    time1 = time.time()
    result = Update.select_all_table()
    time2 = time.time()
    print(time2-time1)
    # new_result, new_wind_data = update_wind_key_targets()
    # path = "../../input/y集合%s.xlsx"% str(date.today())[:10]
    # new_result.to_excel(path)
# =============================================================================
#     Update.update(['yoy'],[path])
# =============================================================================


























